﻿using AppFx.BizTalk.Diagnostics;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using log4net;
using Microsoft.BizTalk.Component.Interop;
using Microsoft.BizTalk.Message.Interop;
using Microsoft.BizTalk.Streaming;
using System;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace AppFx.BizTalk.PipelineComponents
{
    [ComponentCategory(CategoryTypes.CATID_PipelineComponent)]
    [ComponentCategory(CategoryTypes.CATID_Any)]
    [System.Runtime.InteropServices.Guid(ComponentGuid)]
    public class ExcelToXmlConverter : BaseCustomPipelineComponent, IComponent, IComponentUI, IPersistPropertyBag
    {
        private static readonly ILog Log = LogFactory.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

        public const string ComponentGuid = "454BA1FE-588D-4D25-869E-CF5509B35F35";
        public const string ComponentDescription = "Converts Excel 2007+ to Xml using xsls format";
        public const string ComponentName = "ExcelToXmlConverter";

        private string _xmlNamespace;
        private string _xmlRootElementName;


        public ExcelToXmlConverter()
            : base(ComponentName, typeof(LogMessage), ComponentDescription)
        {

        }

        public Microsoft.BizTalk.Message.Interop.IBaseMessage Execute(IPipelineContext pContext, IBaseMessage pInMsg)
        {            
            if (pInMsg == null || pInMsg.BodyPart == null || pInMsg.BodyPart.Data == null)
            {
                throw new ArgumentNullException("pInMsg");
            }

            Log.Debug("Starting pipeline");
            var startElement = string.Format("<{0} xmlns=\"{1}\">", _xmlRootElementName, _xmlNamespace);
            var endElement = string.Format("</{0}>", _xmlRootElementName);
            
            var inboundStream = pInMsg.BodyPart.GetOriginalDataStream();
            var virtualStream = new VirtualStream(VirtualStream.MemoryFlag.OnlyToDisk);
            var readOnlySeekableStream = new ReadOnlySeekableStream(inboundStream, virtualStream);

            var excelAsXml = new StringBuilder();
            excelAsXml.Append(GetXML(readOnlySeekableStream));
            excelAsXml.Replace("<NewDataSet>", startElement);
            excelAsXml.Replace("</NewDataSet>", endElement);

            var outputMsg = CreateNewMessage(excelAsXml.ToString(), pContext, pInMsg);
            Log.Debug("Ending pipeline");

            return outputMsg;
        }

        private IBaseMessage CreateNewMessage(string xmlBody, IPipelineContext pContext, IBaseMessage pInMsg)
        {
            var stream = new MemoryStream();
            var writer = new StreamWriter(stream);
            writer.Write(xmlBody);
            writer.Flush();
            stream.Flush();
            stream.Seek(0, SeekOrigin.Begin);

            var outputMsg = pContext.GetMessageFactory().CreateMessage();
            outputMsg.AddPart("Body", pContext.GetMessageFactory().CreateMessagePart(), true);
            outputMsg.BodyPart.Data = stream;            

            //Promote properties if required.
            for (int iProp = 0; iProp < pInMsg.Context.CountProperties; iProp++)
            {
                string strName;
                string strNSpace;
                object val = pInMsg.Context.ReadAt(iProp, out strName, out strNSpace);

                // If the property has been promoted, respect the settings
                if (pInMsg.Context.IsPromoted(strName, strNSpace))
                    outputMsg.Context.Promote(strName, strNSpace, val);
                else
                    outputMsg.Context.Write(strName, strNSpace, val);                
            }

            return outputMsg;
        }

        public string XmlNamespace
        {
            get { return _xmlNamespace; }
            set { _xmlNamespace = value; }
        }
        public string XmlRootElementName
        {
            get { return _xmlRootElementName; }
            set { _xmlRootElementName = value; }
        }

        #region IComponentUI Members
        /// <summary>
        /// Icon
        /// </summary>
        public IntPtr Icon
        {
            get { return IntPtr.Zero; }
        }
        /// <summary>
        /// Validate
        /// </summary>
        /// <param name="projectSystem"></param>
        /// <returns></returns>
        public System.Collections.IEnumerator Validate(object projectSystem)
        {
            return null;
        }
        #endregion

        #region IPersistPropertyBag Members
        /// <summary>
        /// The guid
        /// </summary>
        /// <param name="classID"></param>
        public void GetClassID(out Guid classID)
        {
            classID = new Guid(ComponentGuid);
        }
        /// <summary>
        /// No implementation
        /// </summary>
        public void InitNew()
        {
        }
        /// <summary>
        /// Loads from property bag
        /// </summary>
        /// <param name="propertyBag"></param>
        /// <param name="errorLog"></param>        
        public void Load(IPropertyBag propertyBag, int errorLog)
        {
            try
            {
                base.ReadPropertyBag(propertyBag, "XmlNamespace", ref this._xmlNamespace);
                base.ReadPropertyBag(propertyBag, "XmlRootElementName", ref this._xmlRootElementName);
            }
            catch (Exception ex)
            {
                string message = string.Format(CultureInfo.InvariantCulture, "IPersistPropertyBag.Load error: {0}", ex.ToString());
                Log.Error(message, ex);
                throw;
            }
        }
        /// <summary>
        /// Sdaves state to property bag
        /// </summary>
        /// <param name="propertyBag"></param>
        /// <param name="clearDirty"></param>
        /// <param name="saveAllProperties"></param>
        public void Save(IPropertyBag propertyBag, bool clearDirty, bool saveAllProperties)
        {
            try
            {
                base.WritePropertyBag(propertyBag, "XmlNamespace", this._xmlNamespace);
                base.WritePropertyBag(propertyBag, "XmlRootElementName", this._xmlRootElementName);
            }
            catch (Exception ex)
            {
                string message = string.Format(CultureInfo.InvariantCulture, "IPersistPropertyBag.Load error: {0}", ex.ToString());
                Log.Error(message, ex);
                throw;
            }
        }
        #endregion

        #region Excel Helpers
        /// <summary> 
        /// Convert DataTable to Xml format 
        /// </summary> 
        /// <param name="filename">Excel File as a stream</param> 
        /// <returns>Xml format string</returns> 
        public string GetXML(Stream excelFileStream)
        {
            using (DataSet ds = new DataSet())
            {
                ds.Tables.Add(ReadExcelFile(excelFileStream));
                return ds.GetXml();
            }
        } 
        private DataTable ReadExcelFile(Stream excelFileStream)
        {
            // Initialize an instance of DataTable 
            DataTable dt = new DataTable();

            try
            {
                // Use SpreadSheetDocument class of Open XML SDK to open excel file 
                using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(excelFileStream, false))
                {
                    // Get Workbook Part of Spread Sheet Document 
                    WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;


                    // Get all sheets in spread sheet document  
                    IEnumerable<Sheet> sheetcollection = spreadsheetDocument.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();


                    // Get relationship Id 
                    string relationshipId = sheetcollection.First().Id.Value;


                    // Get sheet1 Part of Spread Sheet Document 
                    WorksheetPart worksheetPart = (WorksheetPart)spreadsheetDocument.WorkbookPart.GetPartById(relationshipId);


                    // Get Data in Excel file 
                    SheetData sheetData = worksheetPart.Worksheet.Elements<SheetData>().First();
                    IEnumerable<Row> rowcollection = sheetData.Descendants<Row>();


                    if (rowcollection.Count() == 0)
                    {
                        return dt;
                    }


                    // Add columns 
                    foreach (Cell cell in rowcollection.ElementAt(0))
                    {
                        dt.Columns.Add(GetValueOfCell(spreadsheetDocument, cell));
                    }


                    // Add rows into DataTable 
                    foreach (Row row in rowcollection)
                    {
                        DataRow temprow = dt.NewRow();
                        int columnIndex = 0;
                        foreach (Cell cell in row.Descendants<Cell>())
                        {
                            // Get Cell Column Index 
                            int cellColumnIndex = GetColumnIndex(GetColumnName(cell.CellReference));


                            if (columnIndex < cellColumnIndex)
                            {
                                do
                                {
                                    temprow[columnIndex] = string.Empty;
                                    columnIndex++;
                                }


                                while (columnIndex < cellColumnIndex);
                            }


                            temprow[columnIndex] = GetValueOfCell(spreadsheetDocument, cell);
                            columnIndex++;
                        }


                        // Add the row to DataTable 
                        // the rows include header row 
                        dt.Rows.Add(temprow);
                    }
                }


                // Here remove header row 
                dt.Rows.RemoveAt(0);
                return dt;
            }
            catch (IOException ex)
            {
                throw new IOException(ex.Message);
            }
        }
        /// <summary> 
        ///  Get Value of Cell  
        /// </summary> 
        /// <param name="spreadsheetdocument">SpreadSheet Document Object</param> 
        /// <param name="cell">Cell Object</param> 
        /// <returns>The Value in Cell</returns> 
        private static string GetValueOfCell(SpreadsheetDocument spreadsheetdocument, Cell cell)
        {
            // Get value in Cell 
            SharedStringTablePart sharedString = spreadsheetdocument.WorkbookPart.SharedStringTablePart;
            if (cell.CellValue == null)
            {
                return string.Empty;
            }


            string cellValue = cell.CellValue.InnerText;

            // The condition that the Cell DataType is SharedString 
            if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
            {
                return sharedString.SharedStringTable.ChildElements[int.Parse(cellValue)].InnerText;
            }
            else
            {
                return cellValue;
            }
        }

        /// <summary> 
        /// Get Column Name From given cell name 
        /// </summary> 
        /// <param name="cellReference">Cell Name(For example,A1)</param> 
        /// <returns>Column Name(For example, A)</returns> 
        private string GetColumnName(string cellReference)
        {
            // Create a regular expression to match the column name of cell 
            Regex regex = new Regex("[A-Za-z]+");
            Match match = regex.Match(cellReference);
            return match.Value;
        }

        /// <summary> 
        /// Get Index of Column from given column name 
        /// </summary> 
        /// <param name="columnName">Column Name(For Example,A or AA)</param> 
        /// <returns>Column Index</returns> 
        private int GetColumnIndex(string columnName)
        {
            int columnIndex = 0;
            int factor = 1;

            // From right to left 
            for (int position = columnName.Length - 1; position >= 0; position--)
            {
                // For letters 
                if (Char.IsLetter(columnName[position]))
                {
                    columnIndex += factor * ((columnName[position] - 'A') + 1) - 1;
                    factor *= 26;
                }
            }

            return columnIndex;
        }
        #endregion
    }
}
